package com.edu.internship.util;

import com.edu.internship.glabal.constants.Constants;
import com.edu.internship.glabal.constants.ResultCodeEnum;
import com.edu.internship.glabal.exception.BusinessException;
import com.edu.internship.util.excel.ExcelElement;
import com.google.common.collect.Maps;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * code is far away from bug with the animal protecting
 *
 * @author lishiquan
 * @description Excel操作类
 * @date Create in 2018/1/24 15:08
 */
@Slf4j
public class ExcelUtils {

    private static final String DATE_FORMAT_STR = "yyyy-MM-dd HH:mm:ss";

    private ExcelUtils() {
    }

    public static <T> void normalExport(String title, List<T> dataList, List<ExcelElement> elements,
                                        HttpServletResponse response) {
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.createSheet();
        export(workbook, title, dataList, 0, elements, null, response);
    }

    public static <T> void normalExport(String title, List<T> dataList, List<ExcelElement> elements,
                                        Map<String, Object> dictionary, HttpServletResponse response) {
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.createSheet();
        export(workbook, title, dataList, 0, elements, dictionary, response);
    }

    public static <T> void normalExport(Workbook workbook, String title, List<T> dataList, int startRow, List<ExcelElement> elements,
                                        Map<String, Object> dictionary, HttpServletResponse response) {
        export(workbook, title, dataList, startRow, elements, dictionary, response);
    }

    private static <T> void export(Workbook workbook, String title, List<T> dataList, int startRow, List<ExcelElement> elements,
                                   Map<String, Object> dictionary, HttpServletResponse response) {
        CellStyle style = getStyle(workbook);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.createRow(startRow);
        // 将列头设置到sheet的单元格中
        for (int colNum = 0; colNum < elements.size(); colNum++) {
            Cell cell = row.createCell(colNum);
            cell.setCellValue(new XSSFRichTextString(elements.get(colNum).getHeader()));
            cell.setCellStyle(style);
        }
        List<Map<String, Object>> mapList = JsonUtils.getInstance().listBeanToMap(dataList);
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> obj = mapList.get(i);
            row = sheet.createRow(i + 1);
            for (int j = 0; j < elements.size(); j++) {
                Cell cell = row.createCell(j);
                // 设置单元格的值
                cell.setCellValue(validAndSetValue(obj, elements.get(j), dictionary));
                // 设置单元格样式
                cell.setCellStyle(style);
            }
            obj.clear();
        }
        output(title, workbook, response);
    }

    public static void output(String title, Workbook workbook, HttpServletResponse response) {
        try (BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream())) {
            String fileName = title + Constants.BaseCons.DOT.getCode() + Constants.BaseCons.EXCEL_2007_SUFFIX.getCode();
            String trueName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + trueName);
            bos.flush();
            workbook.write(bos);
        } catch (IOException ex) {
            log.error("导出Excel异常: [{}]", ex.getMessage(), ex);
            throw new BusinessException(ResultCodeEnum.DOWNLOAD_FILE_ERROR);
        }
    }

    public static String validAndSetValue(Map<String, Object> obj, ExcelElement excelElement, Map<String, Object> dictionary) {
        String value = "";
        // 如果是0,则全部为字符串,当时null的时候,则转为空字符串""
        if (Tools.equals(ExcelElement.STRING, excelElement.getCheckValue())) {
            value = Tools.nullToSpaceStr((String) obj.get(excelElement.getFieldName()));
        }
        // 如果是1,则是null全部默认为数字0, 输出到excel的0则是字符串
        if (Tools.equals(ExcelElement.NUMERIC, excelElement.getCheckValue())) {
            Object val =  obj.get(excelElement.getFieldName());
            int scale = 0;
            if(Objects.nonNull(excelElement.getFormatter())){
                scale = Integer.parseInt(excelElement.getFormatter());
            }
            if (Objects.isNull(val)){
                value = BigDecimal.ZERO.setScale(scale, RoundingMode.UNNECESSARY).toString();
            } else {
                BigDecimal decimal = new BigDecimal(obj.get(excelElement.getFieldName()).toString())
                        .setScale(scale, RoundingMode.HALF_UP);
                value = decimal.toString();
            }
        }
        // 如果是2,则是日期类型
        if (Tools.equals(ExcelElement.DATE, excelElement.getCheckValue())) {
            Long timestamp = (Long) obj.get(excelElement.getFieldName());
            if (Objects.isNull(timestamp)) {
                value = Tools.EMPTY;
            } else {
                LocalDateTime localDateTime = DateUtils.getLocalDateTimeOfTimestamp(timestamp);
                value = DateUtils.dateFormatter(localDateTime, excelElement.getFormatter());
            }
        }
        // 如果是3,则是字典类型.需要从key值转换成value值.
        if (Tools.equals(ExcelElement.DICT, excelElement.getCheckValue())) {
            value = DictionaryUtils.formatFromCode(
                    Tools.nullToSpaceStr((String) obj.get(excelElement.getFieldName())),
                    dictionary,
                    excelElement.getFormatter()
            );
            // 如果从字典中找不到对应匹配的..则直接返回值
            if (Tools.isBlank(value)) {
                value = Tools.nullToSpaceStr((String) obj.get(excelElement.getFieldName()));
            }
        }
        if (Tools.equals(ExcelElement.SENSITIVE, excelElement.getCheckValue())) {
            value = Tools.nullToSpaceStr((String) obj.get(excelElement.getFieldName()));
            switch (excelElement.getTypeEnum()) {
                case CHINESE_NAME:
                    value = DesensitizedUtils.chineseName(value);
                    break;
                case ID_CARD:
                    value = DesensitizedUtils.idCard(value);
                    break;
                case MOBILE_PHONE:
                    value = DesensitizedUtils.mobile(value);
                    break;
                case EMAIL:
                    value = DesensitizedUtils.email(value);
                    break;
                case BANK_CARD:
                    value = DesensitizedUtils.bankCard(value);
                    break;
                default:
            }
        }
        return value;
    }

    public static <T> List<T> readExcelData(String filePath, List<ExcelElement> elements, Class<T> cls) {
        return readExcelData(StreamUtils.getFileInputStream(filePath), elements, 0, cls);
    }

    public static <T> List<T> readExcelData(String filePath, List<ExcelElement> elements, int startRow, Class<T> cls) {
        return readExcelData(StreamUtils.getFileInputStream(filePath), elements, startRow, cls);
    }

    public static <T> List<T> readExcelData(File file, List<ExcelElement> elements, Class<T> cls) {
        return readExcelData(StreamUtils.getFileInputStream(file), elements, 0, cls);
    }

    public static <T> List<T> readExcelData(InputStream stream, List<ExcelElement> elements, Class<T> cls) {
        return readExcelData(stream, elements, 0, cls);
    }

    public static <T> List<T> readExcelData(InputStream stream, List<ExcelElement> elements, int startRow, Class<T> cls) {
        return readExcelData(stream, 0, elements, startRow, cls);
    }

    public static <T> List<T> readExcelData(InputStream stream, int sheetNum, List<ExcelElement> elements, int startRow, Class<T> cls) {
        byte[] bytes = StreamUtils.inputStreamToByte(stream);
        isExcelFile(bytes);
        try (Workbook workbook = WorkbookFactory.create(StreamUtils.byteToInputStream(bytes))) {
            return readExcelData(workbook, sheetNum, elements, startRow, cls);
        } catch (IOException e) {
            throw new BusinessException(ResultCodeEnum.READ_EXCEL_ERROR, e.getMessage());
        }
    }

    public static <T> List<T> readExcelData(Workbook workbook, int sheetNum, List<ExcelElement> elements, int startRow, Class<T> cls) {
        List<T> list = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(sheetNum);
        Map<Integer, String> headerMap = new HashMap<>(sheet.getRow(0).getLastCellNum());
        if (sheet.getRow(0).getLastCellNum() != elements.size()) {
            throw new BusinessException(ResultCodeEnum.UPLOAD_EXCEL_TEMPLATE_ERROR);
        }
        for (int i = 0; i < elements.size(); i++) {
            headerMap.put(i, elements.get(i).getFieldName());
        }
        for (int rowNum = startRow; rowNum <= sheet.getPhysicalNumberOfRows(); rowNum++) {
            Row excelRow = sheet.getRow(rowNum);
            if (Objects.isNull(excelRow) || excelRow.getPhysicalNumberOfCells() == 0) {
                continue;
            }
            Map<String, Object> row = Maps.newHashMapWithExpectedSize(excelRow.getLastCellNum() * 2);
            for (int colNum = 0; colNum < excelRow.getLastCellNum(); colNum++) {
                Cell cell = isMergedRegion(sheet, rowNum, colNum) ?
                        getMergedRegionCell(sheet, rowNum, colNum) : excelRow.getCell(colNum);
                if (cell != null && cell.getCellType() != CellType.BLANK) {
                    row.put(headerMap.get(colNum), getCellValue(cell));
                }
            }
            if (!row.isEmpty()) {
                list.add(JsonUtils.getInstance().convertToObj(JsonUtils.getInstance().convertToStr(row), cls));
            }
        }
        return list;
    }

    public static Map<String, Integer> mergedRegion(Sheet sheet, int rowNum, int colNum) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        Map<String, Integer> result = Maps.newHashMapWithExpectedSize(8);
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstRowNum = range.getFirstRow();
            int lastRowNum = range.getLastRow();
            int firstColNum = range.getFirstColumn();
            int lastColNum = range.getLastColumn();
            if (rowNum >= firstRowNum && rowNum <= lastRowNum) {
                if (colNum >= firstColNum && colNum <= lastColNum) {
                    result.put("firstRowNum", firstRowNum);
                    result.put("lastRowNum", lastRowNum);
                    result.put("firstColNum", firstColNum);
                    result.put("lastColNum", lastColNum);
                    return result;
                }
            }
        }
        return Maps.newHashMap();
    }

    public static boolean isMergedRegion(Sheet sheet, int rowNum, int colNum) {
        Map<String, Integer> mergedRegion = mergedRegion(sheet, rowNum, colNum);
        return !MapUtils.isEmpty(mergedRegion);
    }

    public static Cell getMergedRegionCell(Sheet sheet, int rowNum, int colNum) {
        Map<String, Integer> mergedRegion = mergedRegion(sheet, rowNum, colNum);
        if (!MapUtils.isEmpty(mergedRegion)) {
            rowNum = mergedRegion.get("firstRowNum");
            colNum = mergedRegion.get("firstColNum");
        }
        return sheet.getRow(rowNum).getCell(colNum);
    }

    public static String getMergedRegionValue(Sheet sheet, int rowNum, int colNum) {
        return getCellValue(getMergedRegionCell(sheet, rowNum, colNum));
    }

    public static String getCellValue(Workbook workbook, int sheetNum, int rowNum, int colNum) {
        return getCellValue(workbook.getSheetAt(sheetNum), rowNum, colNum);
    }

    public static String getCellValue(Sheet sheet, int rowNum, int colNum) {
        return getCellValue(sheet.getRow(rowNum), colNum);
    }

    public static String getCellValue(Sheet sheet, int rowNum, String colNum) {
        return getCellValue(sheet, rowNum, calRowNum(colNum));
    }

    public static String getCellValue(Row row, int colNum) {
        return getCellValue(row.getCell(colNum));
    }

    public static String getCellValue(Cell cell) {
        if (Objects.isNull(cell)) {
            return Tools.EMPTY;
        }
        CellType cellType = cell.getCellType();
        if (cellType == CellType.STRING || cellType == CellType.BLANK) {
            return cell.getStringCellValue()
                    .replaceAll("\r", "")
                    .replaceAll("\t", "")
                    .replaceAll("\n", "")
                    .replaceAll(" ", "").trim();
        } else if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA) {
            if (DateUtil.isCellDateFormatted(cell)) {
                LocalDateTime ldt = DateUtil.getLocalDateTime(cell.getNumericCellValue());
                return String.valueOf(DateUtils.getTimeMilli(ldt));
            }
            BigDecimal cellValue = BigDecimal.valueOf(cell.getNumericCellValue());
            if (MathUtils.isWholeNumber(cellValue)) {
                return cellValue.setScale(0, RoundingMode.UNNECESSARY).toString();
            } else {
                return cellValue.toString();
            }
        } else {
            return "";
        }
    }

    public static int calRowNum(String colNum) {
        StringBuilder builder = new StringBuilder(colNum.toUpperCase());
        char[] charColNum = builder.reverse().toString().toCharArray();
        int sum = -1;
        for (int i = 0, length = charColNum.length; i < length; i++) {
            sum += (charColNum[i] - 64) * Math.pow(26, i);
        }
        return sum;
    }

    public static void mergedRegion(Sheet sheet, Row row, String value, int firstRow, int lastRow, int firstCol, int lastCol) {
        setCellValue(row, value, firstCol);
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);
    }

    public static void setCellValue(Row row, String value, int colNum) {
        Cell cell = row.createCell(colNum);
        cell.setCellValue(new XSSFRichTextString(value));
    }

    public static CellStyle getStyle(Workbook workBook) {
        Font font = workBook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("微软雅黑");
        CellStyle style = workBook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setFont(font);
        style.setWrapText(false);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        DataFormat format = workBook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        return style;
    }

    public static void isExcelFile(byte[] bytes) {
        boolean validateResult = false;
        try (InputStream inputStream = StreamUtils.byteToInputStream(bytes)){
            FileMagic fileMagic = FileMagic.valueOf(inputStream);
            // FileMagic.OOXML  office 2007版本
            if (Objects.equals(fileMagic, FileMagic.OOXML)) {
                validateResult = true;
            }
        } catch (IOException e) {
            log.error("读取excel文件错误:[{}]", e.getMessage());
            throw new BusinessException(ResultCodeEnum.READ_EXCEL_ERROR, "请上传正确的文件");
        }
        if (!validateResult) {
            log.error("校验excel文件头错误");
            throw new BusinessException(ResultCodeEnum.READ_EXCEL_ERROR, "请上传正确的xlsx文件");
        }
    }

}
